import pymysql

class sql_util_class:

    host_user_password_prot_list = []   #"47.101.151.209","root", "root","stock","3309"

    def __init__(self,host_user_password_prot_list):
        self.host_user_password_prot_list = host_user_password_prot_list

    def conn_properties(self):
        conn = pymysql.connect(*self.host_user_password_prot_list)
        return conn
    def close_conn(self,conn):
        conn.close()

    #以dict形式返回
    def fetchall_dic_sql(self,string):
        conn = self.conn_properties()
        cur = conn.cursor(cursor = pymysql.cursors.DictCursor)
        cur.execute(string)
        re = cur.fetchall()
        self.close_conn(conn)
        return re

    def sql_tuple_fetch_all(self,string):
        conn = self.conn_properties()
        cur = conn.cursor()
        cur.execute(string)
        re = cur.fetchall()
        self.close_conn(conn)
        return re
    def execute_sql(self,string):  # 执行sql
        conn = self.conn_properties()
        cur = conn.cursor()
        cur.execute(string)
        conn.commit()
        re = cur.rowcount
        self.close_conn(conn)
        return re
    def execute_sql_with_try_catch_finally(self,string):  # 执行sql
        # global conn
        conn = self.conn_properties()
        re = 0
        try:
            cur = conn.cursor()
            cur.execute(string)
            re = cur.rowcount  # 这个需要在前面
            conn.commit()
            # print(conn.affected_rows())
        except Exception as e:
            # print("")
            print(e)
        finally:
            self.close_conn(conn)
        return re

    def executemany2_sql(self,args):  # 手动多次执行sql列表
        conn = self.conn_properties()
        cur = conn.cursor()
        re = 0
        for a in args:
            cur.execute(a)
            re += cur.rowcount
        conn.commit()
        self.close_conn(conn)
        return re
    def fetchone_sql(self,string, conn=None):  # 执行sql
        # conn = pymysql.connect("121.196.215.190", "wyt", "wyt12345")
        if conn == None:
            conn = self.conn_properties()
        cur = conn.cursor()
        cur.execute(string)
        # conn.commit()
        re = cur.fetchone()
        self.close_conn(conn)
        return re
    def fetchone_one_by_one_sql(self,string_list):#return [[0,(x,x,x,x,)],[1,(x,x,x,x)],[2,(x,x,x,)],[],[]] 序号+结果元祖
        conn = self.conn_properties()
        cur = conn.cursor()
        result_list = []    #[[0,(x,x,x,x,)],[1,(x,x,x,x)],[2,(x,x,x,)],[],[]]
        # for string in string_list:
        #     re = cur.fetchone(string)
        #     one_result = []
        for i in range(0,len(string_list)):
            cur.execute(string_list[i])
            re = cur.fetchone()
            one_result = [i,re]
            result_list.append(one_result)
        self.close_conn(conn)
        return result_list
    def fetchall_sql(self,string):
        # conn = pymysql.connect("121.196.215.190", "wyt", "wyt12345")
        conn = self.conn_properties()
        cur = conn.cursor()
        cur.execute(string)
        # conn.commit()
        re = cur.fetchall()
        self.close_conn(conn)
        return re

    def fetch_with_exception(self,string):
        conn = self.conn_properties()
        cur = conn.cursor()
        # conn.connect_timeout  ： default 10
        try:
            cur.execute(string)
            re = cur.fetchone()
            return re  # 要记得return，不然啥都没有！！！！！
        except:
            return 0
        finally:
            self.close_conn(conn)